<?php

        // Sample form to upload question source into an ORACLE BLOB
        // column using PHP5's OCI8 API.
        //
        // Based on a sample originally found in
        //     http://www.php.net/manual/en/function.ocinewdescriptor.php


        define("ORA_CON_UN", "cscd494");             // username
        define("ORA_CON_PW", "R3tral435");             // password
        define("ORA_CON_DB", "//146.187.134.17:1542/oracle10g"); // connection string

        if (!isset($_FILES['lob_upload'])) {
        ?>
        <html>
        <head>
            <script type="text/javascript" language="javascript" src="submit.js"></script>
        </head>
        
        <body onload="document.getElementById('question_id').focus()">
        <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST" enctype="multipart/form-data">
            <table>
                <tr> <td> Question ID: </td> </tr> 
                <tr> <td> <input type="text" name="question_id" maxlength="10" id="question_id"> </td> </tr> 
                <tr> <td>  <br /> </td> </tr> 
                <tr> <td>  Image Filename: </td> </tr> 
                <tr> <td>  <input type="file" name="lob_upload" id="lob_upload">  </td> </tr> 
                <tr> <td>  <input type="button" value="Upload" onclick="checkForm(this.form)"> </td></tr> 
            </table>
        </form>
        </body>
        </html>
        <?php
        }
        else 
        {
            $myQuestionID = $_POST['question_id'];  // should really be a unique id e.g. a sequence number
            $myFileName = $_FILES['lob_upload']['name'];
            // connection string
            $conn = oci_connect(ORA_CON_UN, ORA_CON_PW, ORA_CON_DB);
         
            // check if the question alread exists
            $query = "SELECT COUNT(question_id) AS TOTAL
                      FROM QUESTION
                      WHERE question_id = :my_question_id";


            $stmt = oci_parse($conn, $query);
            oci_bind_by_name($stmt, ':my_question_id', $myQuestionID); 
            if(!oci_execute($stmt))
                die("Invalid input data! Go back and try again."); 
           
            $row = oci_fetch_assoc($stmt);
            $rowsNum = $row['TOTAL'];
       
            oci_free_statement($stmt);
                        
            $lob = oci_new_descriptor($conn, OCI_D_LOB);
            
            // Insert the BLOB from PHP's tempory upload area    
            if($rowsNum > 0)
            {
$query=
'INSERT INTO source_file (source_file_id, file_name, source_file)'.
'VALUES ((SELECT MAX(source_file_id) FROM source_file) + 1 AS "source_id", '.
':my_file_name, EMPTY_BLOB()) RETURNING source_file INTO :BLOBDATA';
                $stmt = oci_parse($conn, $query);

            oci_bind_by_name($stmt, 'source_id', $source_id);
            }
            else
            {
                $stmt = oci_parse($conn, "INSERT INTO QUESTION (QUESTION_ID, ".
                  "VERSION, QUESTION_TEXT, QUESTION_SOURCE, FILE_NAME) ".
                  "VALUES (:my_question_id, '1', 'Text goes here', ".
                  "EMPTY_BLOB(), :my_file_name) ".
                  "RETURNING QUESTION_SOURCE INTO :BLOBDATA");

            oci_bind_by_name($stmt, ':my_question_id', $myQuestionID); 
            }
            
            oci_bind_by_name($stmt, ':BLOBDATA', $lob, -1, OCI_B_BLOB);
            oci_bind_by_name($stmt, ':my_file_name', $myFileName);
            oci_execute($stmt, OCI_DEFAULT);
                
            if ($lob->savefile($_FILES['lob_upload']['tmp_name'])) 
            {
                if($rowsNum > 0)
                    echo '<script type="text/javascript"> ';
                    echo 'alert("File Successfully Uploaded and ';
                    echo 'Existing Entry Updated!"); </script>';
                else
                    echo '<script type="text/javascript"> ';
                    echo 'alert("File Successfully Uploaded and ';
                    echo 'New Entry Recorded!"); </script>';
            }
            else 
            {
                echo '<script type="text/javascript"> ';
                echo 'alert("Error! Could not Load File! Try Again! "); </script>';
            }
  
            $lob->free();
            oci_free_statement($stmt);


            if($rowsNum > 0)
            {
               $query="INSERT INTO question_source_file ".
                  "(source_file_id, question_id) VALUES ".
                  "(:source_id, :my_question_id)";

               oci_bind_by_name($stmt, ':my_question_id', $myQuestionID); 
               oci_bind_by_name($stmt, ':source_id', $source_id);
               if(oci_execute($stmt, OCI_DEFAULT)) {
                  oci_commit($conn);
               }
               oci_free_statement($stmt);
            }

            oci_close($conn); 
        }
        ?>
